﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CoreFrame.Business;
using System.Data;

namespace MarchEstate.Manager.Dao.ReportQuery
{
    public class ReportManagerDao : AbstractDao
    {
        public DataTable GetCollectAccountData(DateTime bdate,DateTime edate)
        {
            string strsql = @"SELECT   convert(varchar,b.chargedate,23) chargedate,  c.StatCode, d.StatName, c.ItemCode, c.ItemName
                                , SUM(c.RealityFee) AS RealityFee
                                , SUM(CASE WHEN c.RealityFee < 0 THEN c.RealityFee ELSE 0 END)AS backfee
                                , SUM(c.FavourFee) AS favourfee
                                FROM  WY_Account AS a LEFT OUTER JOIN
                                      WY_ChargeMoney AS b ON a.AccountID = b.AccountID LEFT OUTER JOIN
                                      WY_ChargeMoneyDetail AS c ON b.ChargeID = c.ChargeID LEFT OUTER JOIN
                                      WY_StatItem AS d ON c.StatCode = d.Code
                                WHERE     (a.AccountDate >= '"+bdate.Date+@"') AND (a.AccountDate <= '"+edate.Date+@"')
                                GROUP BY b.chargedate,c.ItemCode, c.ItemName, c.StatCode, d.StatName";
            return oleDb.GetDataTable(strsql);
        }
    }
}
